package com.keke.ado.provider;

import com.keke.ado.baseDao;
import com.keke.pojo.Provider;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class providerDaoImpl implements providerDao {
    public int add(Connection connection, Provider provider) throws Exception {
        PreparedStatement preparedStatement = null;
        int flag = 0;
        if(connection!=null){
            String sql = "insert into smbms_provider (proCode,proName,proDesc," +
                    "proContact,proPhone,proAddress,proFax,createdBy,creationDate) " +
                    "values(?,?,?,?,?,?,?,?,?)";
            Object []params = {provider.getProCode(), provider.getProName(), provider.getProDesc(),
                    provider.getProContact(), provider.getProPhone(), provider.getProAddress(),
                    provider.getProFax(), provider.getCreatedBy(), provider.getCreationDate()};
            baseDao.execute(connection,preparedStatement,sql,params);
        }
        return flag;
    }

    public List<Provider> getProviderList(Connection connection, String proName, String proCode,int currentPageNo, int pageSize) throws Exception {
        List<Provider> providerList = new ArrayList<Provider>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        if(connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider where 1=1 ");
            List<Object> list= new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(proName)){
                sql.append(" and proName like ?");
                list.add("%"+proName+"%");
            }
            if(!StringUtils.isNullOrEmpty(proCode)){
                sql.append(" and proCode like ?");
                list.add("%"+proCode+"%");
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object []params = list.toArray();
            System.out.println("sql --- >"+sql.toString());
            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql.toString(),params);
            while(resultSet.next()){
                Provider _provider = new Provider();
                _provider.setId(resultSet.getInt("id"));
                _provider.setProCode(resultSet.getString("proCode"));
                _provider.setProName(resultSet.getString("proName"));
                _provider.setProDesc(resultSet.getString("proDesc"));
                _provider.setProContact(resultSet.getString("proContact"));
                _provider.setProPhone(resultSet.getString("proPhone"));
                _provider.setProAddress(resultSet.getString("proAddress"));
                _provider.setProFax(resultSet.getString("proFax"));
                _provider.setCreationDate(resultSet.getTimestamp("creationDate"));
                providerList.add(_provider);
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return providerList;
    }

    public int deleteProviderById(Connection connection, String delId) throws Exception {
        PreparedStatement preparedStatement=null;
        int flag=0;
        if(connection!=null){
            String sql = "delete from smbms_provider where id=?";
            Object []params= {delId};
            flag=baseDao.execute(connection,preparedStatement,sql,params);
            baseDao.close(null,preparedStatement,null);
        }
        return flag;

    }

    public Provider getProviderById(Connection connection, String id) throws Exception {
        Provider provider=null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        if(connection!=null){
            String sql = "select * from smbms_provider where id = ?";
            Object []params = {id};
            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql,params);
            if(resultSet.next()){
                provider=new Provider();
                provider.setId(resultSet.getInt("id"));
                provider.setProCode(resultSet.getString("proCode"));
                provider.setProName(resultSet.getString("proName"));
                provider.setProDesc(resultSet.getString("proDesc"));
                provider.setProContact(resultSet.getString("proContact"));
                provider.setProPhone(resultSet.getString("proPhone"));
                provider.setProAddress(resultSet.getString("proAddress"));
                provider.setProFax(resultSet.getString("proFax"));
                provider.setCreatedBy(resultSet.getInt("createdBy"));
                provider.setCreationDate(resultSet.getTimestamp("creationDate"));
                provider.setModifyBy(resultSet.getInt("modifyBy"));
                provider.setModifyDate(resultSet.getTimestamp("modifyDate"));
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return provider;
    }

    public int modify(Connection connection, Provider provider) throws Exception {
        int flag=0;
        PreparedStatement preparedStatement=null;
        if(connection!=null){
            String sql ="update smbms_provider set proName=?,proDesc=?,proContact=?," +
                    "proPhone=?,proAddress=?,proFax=?,modifyBy=?,modifyDate=? where id = ? ";
            Object[] params = {provider.getProName(), provider.getProDesc(), provider.getProContact(), provider.getProPhone(), provider.getProAddress(),
                    provider.getProFax(), provider.getModifyBy(), provider.getModifyDate(), provider.getId()};
            flag=baseDao.execute(connection,preparedStatement,sql,params);
            baseDao.close(null,preparedStatement,null);
        }
        return flag;
    }


    //根据用户名或者角色查询用户总数
    public int getProviderCount(Connection connection, String proCode, String proName) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        int count=0;

        if(connection!=null){
            StringBuffer sql= new StringBuffer();
            sql.append("select count(1) as count from smbms_provider p where 1=1");
            ArrayList<Object> list = new ArrayList<Object>();//存放参数

            if(!StringUtils.isNullOrEmpty(proCode)){
                sql.append(" and p.proCode like ?");
                list.add("%"+proCode+"%");//index:0
            }

            if(!StringUtils.isNullOrEmpty(proName)){
                sql.append(" and p.proName like ?");
                list.add("%"+proName+"%");//index:1
            }

            //把list转为数组
            Object[] params = list.toArray();
            System.out.println("sql:"+sql.toString());

            resultSet = baseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);

            if(resultSet.next()){
                count=resultSet.getInt("count");  //从结果集中获取最终的数量
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return  count;
    }

}
